﻿using System;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Web;

namespace Jerkymon.Data
{

    public enum ConnectionType
    {

        SQLServer,

        ODBC,

        OLE

    }


    #region QueryCounter class
    public sealed class QueryCounter : System.IDisposable
    {
		#region Static Methods (1)
		static public void Reset()
        {
            HttpContext.Current.Items["NumQueries"] = 0;
            HttpContext.Current.Items["TimeQueries"] = (double)0;
            HttpContext.Current.Items["CmdQueries"] = "";

        }
		#endregion

		#region Fields (2)
		private HiPerfTimer hiTimer = new HiPerfTimer(true);

		private string m_cmd;
		#endregion

		#region Properties (3)
		static public string Commands
        {
            get
            {
                return (string)HttpContext.Current.Items["CmdQueries"];

            }
        }
		static public int Count
        {
            get
            {

                return (int)HttpContext.Current.Items["NumQueries"];

            }
        }
		static public double Duration
        {
            get
            {
                return (double)HttpContext.Current.Items["TimeQueries"];

            }
        }
		#endregion

		#region Constructors (1)
		public QueryCounter(string sql)
        {
#if DEBUG
            m_cmd = sql;

            if (HttpContext.Current.Items["NumQueries"] == null)
                HttpContext.Current.Items["NumQueries"] = (int)1;
            else
                HttpContext.Current.Items["NumQueries"] = 1 + (int)HttpContext.Current.Items["NumQueries"];

            //if (cm.Get("NumQueries") == null)
            //    cm.Add("NumQueries", (int)1);
            //else
            //{
            //    int nq = (int)cm.Get("NumQueries");
            //    cm.Remove("NumQueries");
            //    //HttpContext.Current.Items["NumQueries"] = 1 + (int)HttpContext.Current.Items["NumQueries"];
            //    cm.Add("NumQueries", nq + 1);
            //}

#endif
        }
		#endregion

		#region Methods (1)

		public void Dispose()
        {
#if DEBUG
            hiTimer.Stop();

            m_cmd = string.Format("{0}: {1:N3}", m_cmd, hiTimer.Duration);

            if (HttpContext.Current.Items["TimeQueries"] == null)
                HttpContext.Current.Items["TimeQueries"] = hiTimer.Duration;
            else
                HttpContext.Current.Items["TimeQueries"] = hiTimer.Duration + (double)HttpContext.Current.Items["TimeQueries"];

            if (HttpContext.Current.Items["CmdQueries"] == null)
                HttpContext.Current.Items["CmdQueries"] = m_cmd;
            else
                HttpContext.Current.Items["CmdQueries"] += "<br/>" + m_cmd;


#endif
        }

		#endregion
    }
    #endregion
    /// <summary>
    /// Summary description for DataLayer.
    /// </summary>
    public class DataLayer
    {
		#region Static Fields (1)
		private static IsolationLevel m_isoLevel = IsolationLevel.ReadUncommitted;
		#endregion

		#region Static Methods (21)
		/// <summary>
        /// Gets the database size
        /// </summary>
        /// <returns>intager value for database size</returns>
        static public int DBSize(string connstr)
        {
            using (SqlCommand cmd = new SqlCommand("select sum(size) * 8 * 1024 from sysfiles"))
            {
                cmd.CommandType = CommandType.Text;
                return (int)ExecuteScalar(cmd, connstr);
            }
        }

		/// <summary>
        /// Executes a NonQuery
        /// </summary>
        /// <param name="cmd">NonQuery to execute</param>
        static public void ExecuteNonQuery(SqlCommand cmd, string connstr)
        {
            //QueryCounter qc = new QueryCounter(cmd.CommandText);
            try
            {
                using (SqlConnection conn = GetConnection(connstr))
                {
                    using (SqlTransaction trans = conn.BeginTransaction(m_isoLevel))
                    {
                        cmd.Connection = conn;
                        cmd.Transaction = trans;
                        cmd.ExecuteNonQuery();
                        trans.Commit();
                    }
                }
            }
            finally
            {
                //qc.Dispose();
            }
        }

		static public SqlDataReader ExecuteReader(SqlCommand cmd, string connstr)
        {
            //QueryCounter qc = new QueryCounter(cmd.CommandText);
            try
            {
                using (SqlConnection conn = GetConnection(connstr))
                {
                    using (SqlTransaction trans = conn.BeginTransaction(m_isoLevel))
                    {
                        cmd.Connection = conn;
                        cmd.Transaction = trans;
                        SqlDataReader res = cmd.ExecuteReader();
                        trans.Commit();
                        return res;
                    }
                }
            }
            finally
            {
                //qc.Dispose();
            }
        }

		static public T ExecuteScalar<T>(SqlCommand cmd, string connstr)
        {
            //QueryCounter qc = new QueryCounter(cmd.CommandText);
            try
            {
                using (SqlConnection conn = GetConnection(connstr))
                {

                    using (SqlTransaction trans = conn.BeginTransaction(m_isoLevel))
                    {
                        cmd.Connection = conn;
                        cmd.Transaction = trans;
                        T res = (T)cmd.ExecuteScalar();
                        trans.Commit();
                        return res;
                    }
                }
            }
            finally
            {
                //qc.Dispose();
            }
        }

		static public object ExecuteScalar(SqlCommand cmd, string connstr)
        {
            //QueryCounter qc = new QueryCounter(cmd.CommandText);
            try
            {
                using (SqlConnection conn = GetConnection(connstr))
                {
                    using (SqlTransaction trans = conn.BeginTransaction(m_isoLevel))
                    {
                        cmd.Connection = conn;
                        cmd.Transaction = trans;
                        object res = cmd.ExecuteScalar();
                        trans.Commit();
                        return res;
                    }
                }
            }
            finally
            {
                //qc.Dispose();
            }
        }

		static public TReturn ExecuteScalar<TReturn, TConnection, TCommand>(TConnection oCon, TCommand oCmd, string connstr)
        {
            try
            {
                //GetConnection<TConnection>(ref oCon, connstr);

                oCon = GetConnection<TConnection>(oCon, connstr);

                //                using((IDbConnection) oCon)
                //                {
                ((IDbCommand)oCmd).Connection = (IDbConnection)oCon;
                TReturn res = (TReturn)((IDbCommand)oCmd).ExecuteScalar();

                //                }
                ((IDbConnection)oCon).Close();
                ((IDbConnection)oCon).Dispose();
                ((IDbCommand)oCmd).Dispose();
                return res;
            }
            finally
            {
                //qc.Dispose();
            }
        }

		static public TReturn ExecuteScalar<TReturn, TConnection, TTransaction, TCommand>(TConnection oCon, TTransaction oTran, TCommand oCmd, string connstr)
        {
            try
            {
                GetConnection<TConnection>(ref oCon, connstr);
                oTran = (TTransaction)Convert.ChangeType(((IDbConnection)oCon).BeginTransaction(m_isoLevel), ((IDbConnection)oCon).BeginTransaction(m_isoLevel).GetType());
                ((IDbCommand)oCmd).Connection = (IDbConnection)oCon;
                TReturn res = (TReturn)((IDbCommand)oCmd).ExecuteScalar();
                ((IDbTransaction)oTran).Commit();

                ((IDbConnection)oCon).Close();
                ((IDbConnection)oCon).Dispose();
                ((IDbTransaction)oTran).Dispose();
                ((IDbCommand)oCmd).Dispose();
                return res;
            }
            finally
            {
                //qc.Dispose();
            }
        }

		/// <summary>
        /// Gets Connection out of Web.config
        /// </summary>
        /// <returns>Returns SqlConnection</returns>
        public static SqlConnection GetConnection(string connstr)
        {
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            return conn;
        }

		public static void GetConnection<TConnection>(ref TConnection oCon, string connstr)
        {
            //SqlConnection conn = new SqlConnection(connstr);
            //conn.Open();
            //return conn;
            ((IDbConnection)oCon).ConnectionString = connstr;
            ((IDbConnection)oCon).Open();
            //return oCon;
        }

		public static Connection GetConnection<Connection>(Connection oCon, string connstr)
        {
            ((IDbConnection)oCon).ConnectionString = connstr;
            ((IDbConnection)oCon).Open();
            return oCon;
        }

		static public DataSet GetDataSet<TAdapter>(TAdapter da)
        {
            DataSet dst = default(DataSet);
            ((IDbDataAdapter)da).SelectCommand.Connection.Open();
            using (DataSet ds = new DataSet())
            {

                ((IDbDataAdapter)da).Fill(ds);
                dst = ds;

            }
            ((IDbDataAdapter)da).SelectCommand.Connection.Close();
            //GetDataTable<IDbConnection, IDbCommand, IDbTransaction, TAdapter>
            //    (((IDbDataAdapter)da).SelectCommand.Connection, ((IDbDataAdapter)da).SelectCommand,
            //    ((IDbDataAdapter)da).SelectCommand.Connection.BeginTransaction(), da, ((IDbDataAdapter)da).SelectCommand.Connection.ConnectionString);
            return dst;
        }

		/// <summary>
        /// Gets data out of the database
        /// </summary>
        /// <param name="cmd">The SQL Command</param>
        /// <returns>DataTable with the results</returns>
        static public DataSet GetDataSet(SqlCommand cmd, string connstr)
        {
            //QueryCounter qc = new QueryCounter(cmd.CommandText);
            try
            {
                if (cmd.Connection != null)
                {
                    using (DataSet ds = new DataSet())
                    {
                        using (SqlDataAdapter da = new SqlDataAdapter())
                        {
                            da.SelectCommand = cmd;
                            da.Fill(ds);
                            return ds;
                        }
                    }
                }
                else
                {
                    using (SqlConnection conn = GetConnection(connstr))
                    {
                        using (SqlTransaction trans = conn.BeginTransaction(m_isoLevel))
                        {
                            try
                            {
                                cmd.Transaction = trans;
                                using (DataSet ds = new DataSet())
                                {
                                    using (SqlDataAdapter da = new SqlDataAdapter())
                                    {
                                        da.SelectCommand = cmd;
                                        da.SelectCommand.Connection = conn;
                                        da.Fill(ds);
                                        return ds;
                                    }
                                }
                            }
                            finally
                            {
                                trans.Commit();
                            }
                        }
                    }
                }
            }
            finally
            {
                //qc.Dispose();
            }
        }

		/// <summary>
        /// GetDataSet Return as Dataset
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connstr"></param>
        /// <returns>Dataset</returns>		
        static public DataSet GetDataSet(string sql, string connstr)
        {
            //QueryCounter qc = new QueryCounter(sql);
            try
            {
                using (SqlConnection conn = GetConnection(connstr))
                {
                    using (SqlTransaction trans = conn.BeginTransaction(m_isoLevel))
                    {
                        try
                        {
                            using (SqlCommand cmd = conn.CreateCommand())
                            {
                                cmd.Transaction = trans;
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = sql;
                                using (DataSet ds = new DataSet())
                                {
                                    using (SqlDataAdapter da = new SqlDataAdapter())
                                    {
                                        da.SelectCommand = cmd;
                                        da.SelectCommand.Connection = conn;
                                        da.Fill(ds);
                                        return ds;
                                    }
                                }
                            }
                        }
                        finally
                        {
                            trans.Commit();
                        }
                    }
                }
            }
            finally
            {
                //qc.Dispose();
            }
        }

		static public DataSet GetDataSet<TAdapter>(TAdapter da, string connstr)
        {
            DataSet ds = default(DataSet);
            GetDataSet<IDbConnection, IDbCommand, IDbTransaction, TAdapter>
                (((IDbDataAdapter)da).SelectCommand.Connection, ((IDbDataAdapter)da).SelectCommand,
                ((IDbDataAdapter)da).SelectCommand.Connection.BeginTransaction(), da, ((IDbDataAdapter)da).SelectCommand.Connection.ConnectionString);
            return ds;
        }

		/// <summary>
        /// Gets data out of the database
        /// </summary>
        /// <param name="cmd">The SQL Command</param>
        /// <returns>DataSet with the results</returns>
        static public DataSet GetDataSet(SqlCommand cmd, string connstr, int CurrentPage, int PageSize)
        {
            //QueryCounter qc = new QueryCounter(cmd.CommandText);
            int startRecord = (CurrentPage - 1) * PageSize;

            try
            {
                if (cmd.Connection != null)
                {
                    using (DataSet ds = new DataSet())
                    {
                        using (SqlDataAdapter da = new SqlDataAdapter())
                        {
                            da.SelectCommand = cmd;
                            da.Fill(ds, startRecord, PageSize, "record");
                            return ds;
                        }
                    }
                }
                else
                {
                    using (SqlConnection conn = GetConnection(connstr))
                    {
                        using (SqlTransaction trans = conn.BeginTransaction(m_isoLevel))
                        {
                            try
                            {
                                cmd.Transaction = trans;
                                using (DataSet ds = new DataSet())
                                {
                                    using (SqlDataAdapter da = new SqlDataAdapter())
                                    {
                                        da.SelectCommand = cmd;
                                        da.SelectCommand.Connection = conn;
                                        da.Fill(ds, startRecord, PageSize, "record");
                                        return ds;
                                    }
                                }
                            }
                            finally
                            {
                                trans.Commit();
                            }
                        }
                    }
                }
            }
            finally
            {
                //qc.Dispose();
            }
        }

		static public DataSet GetDataSet<TConnection, TCommand, TTransaction, TAdapter>(TConnection conn, TCommand cmd, TTransaction tran, TAdapter da, string connstr)
        {
            //QueryCounter qc = new QueryCounter(cmd.CommandText);
            try
            {
                if (((IDbCommand)cmd).Connection != null)
                {
                    using (DataSet ds = new DataSet())
                    {
                        //using ((IDbDataAdapter)da)
                        //{
                        ((IDbDataAdapter)da).SelectCommand = ((IDbCommand)cmd);
                        ((IDbDataAdapter)da).Fill(ds);

                        return ds;
                        //}
                    }
                }
                else
                {
                    GetConnection<TConnection>(ref conn, connstr);

                    tran = (TTransaction)Convert.ChangeType(((IDbConnection)conn).BeginTransaction(m_isoLevel), ((IDbConnection)conn).BeginTransaction(m_isoLevel).GetType());

                    using (DataSet ds = new DataSet())
                    {
                        //using ((IDbDataAdapter)da)
                        //{

                        ((IDbCommand)cmd).Transaction = (IDbTransaction)tran;
                        ((IDbDataAdapter)da).SelectCommand = ((IDbCommand)cmd);
                        ((IDbDataAdapter)da).SelectCommand.Connection = (IDbConnection)conn;
                        ((IDbDataAdapter)da).Fill(ds);

                        return ds;
                        //}
                    }


                    //using (SqlConnection conn = GetConnection(connstr))
                    //{
                    //    using (SqlTransaction trans = conn.BeginTransaction(m_isoLevel))
                    //    {
                    //        try
                    //        {
                    //            cmd.Transaction = trans;
                    //            using (DataSet ds = new DataSet())
                    //            {
                    //                using (SqlDataAdapter da = new SqlDataAdapter())
                    //                {
                    //                    da.SelectCommand = cmd;
                    //                    da.SelectCommand.Connection = conn;
                    //                    da.Fill(ds);

                    //                    return ds.Tables[0];
                    //                }
                    //            }
                    //        }
                    //        finally
                    //        {
                    //            trans.Commit();
                    //        }
                    //    }
                    //}
                }
            }
            finally
            {
                //qc.Dispose();
            }
        }

		/// <summary>
        /// Gets data out of database using a plain text string command
        /// </summary>
        /// <param name="sql">string command to be executed</param>
        /// <returns>DataTable with results</returns>
        static public DataTable GetDataTable(string sql, string connstr)
        {
            //QueryCounter qc = new QueryCounter(sql);
            try
            {
                using (SqlConnection conn = GetConnection(connstr))
                {
                    using (SqlTransaction trans = conn.BeginTransaction(m_isoLevel))
                    {
                        try
                        {
                            using (SqlCommand cmd = conn.CreateCommand())
                            {
                                cmd.Transaction = trans;
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = sql;
                                using (DataSet ds = new DataSet())
                                {
                                    using (SqlDataAdapter da = new SqlDataAdapter())
                                    {
                                        da.SelectCommand = cmd;
                                        da.SelectCommand.Connection = conn;
                                        da.Fill(ds);
                                        return ds.Tables[0];
                                    }
                                }
                            }
                        }
                        finally
                        {
                            trans.Commit();
                        }
                    }
                }
            }
            finally
            {
                //qc.Dispose();
            }
        }

		/// <summary>
        /// Gets data out of the database
        /// </summary>
        /// <param name="cmd">The SQL Command</param>
        /// <returns>DataTable with the results</returns>
        static public DataTable GetDataTable(SqlCommand cmd, string connstr)
        {
            //QueryCounter qc = new QueryCounter(cmd.CommandText);
            try
            {
                if (cmd.Connection != null)
                {
                    using (DataSet ds = new DataSet())
                    {
                        using (SqlDataAdapter da = new SqlDataAdapter())
                        {
                            da.SelectCommand = cmd;
                            da.Fill(ds);
                            return ds.Tables[0];
                        }
                    }
                }
                else
                {
                    using (SqlConnection conn = GetConnection(connstr))
                    {
                        using (SqlTransaction trans = conn.BeginTransaction(m_isoLevel))
                        {
                            try
                            {
                                cmd.Transaction = trans;
                                using (DataSet ds = new DataSet())
                                {
                                    using (SqlDataAdapter da = new SqlDataAdapter())
                                    {
                                        da.SelectCommand = cmd;
                                        da.SelectCommand.Connection = conn;
                                        da.Fill(ds);
                                        return ds.Tables[0];
                                    }
                                }
                            }
                            finally
                            {
                                trans.Commit();
                            }
                        }
                    }
                }
            }
            finally
            {
                //qc.Dispose();
            }
        }

		/// <summary>
        /// Gets data out of database using a plain text string command
        /// </summary>
        /// <param name="sql">string command to be executed</param>
        /// <returns>DataTable with results</returns>
        static public DataTable GetDataTable(string sql, string connstr, int CurrentPage, int PageSize)
        {
            //QueryCounter qc = new QueryCounter(sql);
            int startRecord = (CurrentPage - 1) * PageSize;

            try
            {
                using (SqlConnection conn = GetConnection(connstr))
                {
                    using (SqlTransaction trans = conn.BeginTransaction(m_isoLevel))
                    {
                        try
                        {
                            using (SqlCommand cmd = conn.CreateCommand())
                            {
                                cmd.Transaction = trans;
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = sql;
                                using (DataSet ds = new DataSet())
                                {
                                    using (SqlDataAdapter da = new SqlDataAdapter())
                                    {
                                        da.SelectCommand = cmd;
                                        da.SelectCommand.Connection = conn;
                                        da.Fill(ds, startRecord, PageSize, "record");
                                        return ds.Tables[0];
                                    }
                                }
                            }
                        }
                        finally
                        {
                            trans.Commit();
                        }
                    }
                }
            }
            finally
            {
                //qc.Dispose();
            }
        }

		static public DataTable GetDataTable<Connection, Command, Adapter>(Connection conn, Command cmd, Adapter da, string connstr)
        {
            try
            {


                if (((IDbCommand)cmd).Connection != null)
                {
                    using (DataSet ds = new DataSet())
                    {
                        ((IDbDataAdapter)da).SelectCommand = ((IDbCommand)cmd);
                        ((IDbDataAdapter)da).Fill(ds);
                        return ds.Tables[0];
                    }
                }
                else
                {
                    conn = GetConnection<Connection>(conn, connstr);

                    try
                    {

                        using (DataSet ds = new DataSet())
                        {
                            ((IDbCommand)cmd).Connection = (IDbConnection)conn;
                            ((IDbDataAdapter)da).SelectCommand = ((IDbCommand)cmd);
                            ((IDbDataAdapter)da).Fill(ds);
                            return ds.Tables[0];
                        }
                    }
                    finally
                    {

                    }


                }
            }
            finally
            {
                //qc.Dispose();
            }
        }

		public static string GetType(IDbConnection oConn)
        {
            OleDbConnection Conn1;
            OdbcConnection Conn2;
            SqlConnection Conn3;
            try
            {
                Conn1 = (OleDbConnection)oConn;
                if (Conn1 != null)
                    return "OLEDB";
            }
            catch
            {
            }
            try
            {
                Conn2 = (OdbcConnection)oConn;
                if (Conn2 != null)
                    return "ODBC";
            }
            catch
            {
            }
            try
            {
                Conn3 = (SqlConnection)oConn;
                if (Conn3 != null)
                    return "SQLSERVER";
            }
            catch
            {
            }
            return "UNKNOWN";
        }
		#endregion

		#region Properties (1)
		static public IsolationLevel IsolationLevel
        {
            get
            {
                return m_isoLevel;
            }
        }
		#endregion
    }
}
